package com.thh.datamonitor.executor;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.thh.common.core.page.PageDomain;
import com.thh.common.core.page.TableSupport;
import com.thh.common.enums.DatabaseDriverEnums;
import com.thh.common.exception.ServiceException;
import com.thh.common.exception.sql.SqlException;
import com.thh.common.utils.StringUtils;
import com.thh.common.utils.sql.SqlUtil;
import com.thh.datamonitor.annotation.DbType;
import com.thh.datamonitor.config.DataMonitorConfig;
import com.thh.datamonitor.dto.TableInfoListDto;
import com.thh.datamonitor.dto.TmdTableDto;
import com.thh.datamonitor.entity.TmdDatabase;
import com.thh.datamonitor.param.TmdTableQueryParam;
import com.thh.datamonitor.executor.dao.MySQLTableDao;
import com.thh.core.manager.DataSourcePoolManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLNonTransientException;
import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@DbType(DatabaseDriverEnums.MYSQL)
public class MysqlExecutor extends AbstractDbExecutor {
    private final static Logger logger = LoggerFactory.getLogger(MysqlExecutor.class);

    @Autowired
    private DataSourcePoolManager dataSourcePoolManager;

    @Autowired
    private DataMonitorConfig dataMonitorConfig;

    @Autowired
    MySQLTableDao mySQLTableDao;

    @Override
    public String genUrl(TmdDatabase database) {
        String url = "jdbc:" + database.getDatabaseType() + "://" + database.getDatabaseHost() + ":" + database.getDatabasePort() + "/" + database.getDatabaseDbname() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true";
        return url;
    }

    @Override
    public synchronized Connection getConnection(TmdDatabase database) {
        Connection connection = null;
        String dataPassword = super.getDataPassword(database);
        String dbId = String.valueOf(database.getDatabaseId());
        DruidDataSource druidDataSource = dataSourcePoolManager.getDataSourcePoolManagerMap().get(dbId);
        if (null == druidDataSource) {
            druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(genUrl(database));
            druidDataSource.setDriverClassName(DatabaseDriverEnums.MYSQL.getDriverClassName());
            druidDataSource.setUsername(database.getDatabaseUser());
            druidDataSource.setPassword(dataPassword);
            druidDataSource.setInitialSize(dataMonitorConfig.getDatabaseInitialSize());
            druidDataSource.setMaxActive(dataMonitorConfig.getDatabaseMaxActive());
            druidDataSource.setMinIdle(dataMonitorConfig.getDatabaseMinIdle());
            druidDataSource.setMaxWait(dataMonitorConfig.getMaxWait());
            druidDataSource.setRemoveAbandonedTimeoutMillis(dataMonitorConfig.getRemoveTimeout());
            druidDataSource.setPoolPreparedStatements(true);
            druidDataSource.setMaxOpenPreparedStatements(20);
            druidDataSource.setValidationQuery("SELECT 1");
            druidDataSource.setTestWhileIdle(true);
            druidDataSource.setTestOnBorrow(false);
            druidDataSource.setTestOnReturn(true);
            try {
                druidDataSource.init();
                dataSourcePoolManager.getDataSourcePoolManagerMap().put(dbId, druidDataSource);
            } catch (Exception e) {
                throw new ServiceException("初始化数据源失败！" + e.getMessage());
            }
        }

        try {
            connection = druidDataSource.getConnection();
        } catch (Exception e) {
            throw new SqlException(database.getDatabaseId(), "获取连接失败！" + e.getMessage());
        }
        return connection;
    }

    @Override
    public List<TmdTableDto> getTableList(TmdDatabase database, TmdTableQueryParam tmdTableQueryParam) {
        Connection connection = getConnection(database);
        List<TmdTableDto> tableDtoList = null;
        try {
            tableDtoList = mySQLTableDao.queryList(connection, database.getDatabaseDbname(), tmdTableQueryParam.getTableName());
        } catch (Exception e) {
            throw new SqlException(database.getDatabaseId(), e.getMessage());
        } finally {
            close(connection);
        }
        return tableDtoList;
    }

    @Override
    public String ddl(TmdDatabase database, TmdTableQueryParam tmdTableQueryDto) {
        if (StringUtils.isBlank(tmdTableQueryDto.getTableName())) {
            throw new ServiceException("表名称不能为空！");
        }
        Connection connection = getConnection(database);
        String ddl = null;
        try {
            ddl = mySQLTableDao.ddl(connection, database.getDatabaseDbname(), tmdTableQueryDto.getTableName());
        } catch (Exception e) {
            throw new SqlException(database.getDatabaseId(), e.getMessage());
        } finally {
            close(connection);
        }
        if (StringUtils.isBlank(ddl)) {
            throw new SqlException(database.getDatabaseId(), "查询ddl失败");
        }
        return ddl;
    }

    @Override
    public String getPrimaryKey(TmdDatabase database, String tableName) {
        Connection connection = getConnection(database);
        String primaryKey = null;
        try {
            primaryKey = mySQLTableDao.getPrimaryKey(connection, database.getDatabaseDbname(), tableName);
        } catch (Exception e) {
            throw new SqlException(database.getDatabaseId(), e.getMessage());
        } finally {
            close(connection);
        }
        return primaryKey;
    }

    @Override
    public TableInfoListDto executeSelectSql(TmdDatabase database, String sql) {
        TableInfoListDto tableInfoListDto = new TableInfoListDto();
        Connection conn = null;
        try {
            conn = getConnection(database);
            PageDomain pageDomain = TableSupport.buildPageRequest();
            int pageNum = pageDomain.getPageNum();
            int pageSize = pageDomain.getPageSize();
            String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());

            if (!StrUtil.hasEmpty(orderBy)) {
                sql += " order by " + orderBy;
            }
            int limitFrom = (pageNum - 1) * pageSize;
            sql = sql.trim();
            String finalSql = sql;
            if (!SqlUtil.isSpecialQuerySql(sql)) {
                finalSql = " select * from ( " + sql + " ) tab  LIMIT " + limitFrom + "," + pageSize;
            }

            List<Map<String, Object>> list = super.queryForList(conn, finalSql);
            long rowCount = super.executeQueryForCount2(conn, sql);

            List<Map<String, Object>> columnTypeList = super.executeSqlForColumns(conn, sql);
            Map columnTypeMap = new HashMap();
            List<Map<String, Object>> columns = new ArrayList<>();
            for (Map<String, Object> map : columnTypeList) {
                columnTypeMap.put(map.get("column_name"), ((String) map.get("data_type")).toLowerCase());

                Map<String, Object> data = new HashMap<>();
                data.put("field", map.get("column_name"));
                data.put("title", map.get("column_name"));
                data.put("sortable", Boolean.valueOf(true));
                if (map.get("data_type").equals("DATETIME")) {
                    data.put("editor", "datetimebox");
                } else if ((map.get("data_type").equals("INT")) || (map.get("data_type").equals("SMALLINT")) || (map.get("data_type").equals("TINYINT"))) {
                    data.put("editor", "numberbox");
                } else if (map.get("data_type").equals("DOUBLE")) {
                    data.put("editor", "numberbox");
                } else if ((!map.get("data_type").equals("BLOB")) && (!map.get("data_type").equals("CLOB"))) {
                    if ((!map.get("data_type").equals("VARBINARY")) && (!map.get("data_type").equals("BINARY"))) {
                        data.put("editor", "text");
                    }
                }
                columns.add(data);
            }
            tableInfoListDto.setTotal(rowCount);
            tableInfoListDto.setRows(list);
            tableInfoListDto.setColumns(columns);
            tableInfoListDto.setColumnType(columnTypeMap);
            tableInfoListDto.setPageNum(pageNum);
            tableInfoListDto.setPageSize(pageSize);
            tableInfoListDto.setExecuteSql(sql);
        } catch (SQLSyntaxErrorException e) {
            throw new SqlException(database.getDatabaseId(), e.getErrorCode() + " - "+ e.getMessage());
        } catch (SQLException e) {
            throw new SqlException(database.getDatabaseId(), e.getMessage());
        } finally {
            close(conn);
        }

        return tableInfoListDto;
    }
}
